USE master;
GO

-- create a queue
DECLARE
    @ReturnCode INT,
    @TraceID INT,
    @TraceName NVARCHAR(245) = N'C:\Temp\TS_' +
        CONVERT(SYSNAME, SERVERPROPERTY('MachineName')),
    @MaxFileSize BIGINT = 200,
	@FileCount INT = 5;

-- the .trc extension will be appended to the filename automatically
EXECUTE @ReturnCode = sp_trace_create @TraceID OUTPUT, 2, @TraceName, @MaxFileSize, NULL, @FileCount;

-- set the events
EXECUTE sp_trace_setevent @TraceID, 10, 16, 1;
EXECUTE sp_trace_setevent @TraceID, 10, 48, 1;
EXECUTE sp_trace_setevent @TraceID, 10, 1,  1;
EXECUTE sp_trace_setevent @TraceID, 10, 17, 1;
EXECUTE sp_trace_setevent @TraceID, 10, 10, 1;
EXECUTE sp_trace_setevent @TraceID, 10, 18, 1;
EXECUTE sp_trace_setevent @TraceID, 10, 3,  1;
EXECUTE sp_trace_setevent @TraceID, 10, 11, 1;
EXECUTE sp_trace_setevent @TraceID, 10, 35, 1;
EXECUTE sp_trace_setevent @TraceID, 10, 12, 1;
EXECUTE sp_trace_setevent @TraceID, 10, 13, 1;
EXECUTE sp_trace_setevent @TraceID, 10, 6,  1;
EXECUTE sp_trace_setevent @TraceID, 45, 16, 1;
EXECUTE sp_trace_setevent @TraceID, 45, 48, 1;
EXECUTE sp_trace_setevent @TraceID, 45, 1,  1;
EXECUTE sp_trace_setevent @TraceID, 45, 17, 1;
EXECUTE sp_trace_setevent @TraceID, 45, 10, 1;
EXECUTE sp_trace_setevent @TraceID, 45, 18, 1;
EXECUTE sp_trace_setevent @TraceID, 45, 3,  1;
EXECUTE sp_trace_setevent @TraceID, 45, 11, 1;
EXECUTE sp_trace_setevent @TraceID, 45, 35, 1;
EXECUTE sp_trace_setevent @TraceID, 45, 12, 1;
EXECUTE sp_trace_setevent @TraceID, 45, 28, 1;
EXECUTE sp_trace_setevent @TraceID, 45, 13, 1;
EXECUTE sp_trace_setevent @TraceID, 45, 6,  1;
EXECUTE sp_trace_setevent @TraceID, 12, 16, 1;
EXECUTE sp_trace_setevent @TraceID, 12, 48, 1;
EXECUTE sp_trace_setevent @TraceID, 12, 1,  1;
EXECUTE sp_trace_setevent @TraceID, 12, 17, 1;
EXECUTE sp_trace_setevent @TraceID, 12, 6,  1;
EXECUTE sp_trace_setevent @TraceID, 12, 10, 1;
EXECUTE sp_trace_setevent @TraceID, 12, 18, 1;
EXECUTE sp_trace_setevent @TraceID, 12, 3,  1;
EXECUTE sp_trace_setevent @TraceID, 12, 11, 1;
EXECUTE sp_trace_setevent @TraceID, 12, 35, 1;
EXECUTE sp_trace_setevent @TraceID, 12, 12, 1;
EXECUTE sp_trace_setevent @TraceID, 12, 13, 1;

-- set the filters (reads > 1000, exclude system events)
DECLARE
	@Reads BIGINT = 1000, @IsSystem INT = 0;
EXECUTE sp_trace_setfilter @TraceID, 16, 0, 4, @Reads;
EXECUTE sp_trace_setfilter @TraceID, 35, 0, 6, N'<databasename>';
EXECUTE sp_trace_setfilter @TraceID, 60, 0, 0, @IsSystem;

-- set the trace status to start
EXECUTE sp_trace_setstatus @TraceID, 1;

-- display trace id and trace name
SELECT TraceID = @TraceID, TraceFile = @TraceName + N'.trc';

-- monitor the workload

-- view running traces on the system
SELECT * FROM sys.traces;

-- get trace event info
SELECT 
 	e.name AS event_name, 
 	c.name AS column_name
FROM fn_trace_geteventinfo(<traceid>) i
INNER JOIN sys.trace_events e ON i.eventid = e.trace_event_id 
INNER JOIN sys.trace_columns c ON i.columnid = c.trace_column_id;

-- stop the trace, but keep its definition on the server
EXECUTE sp_trace_setstatus <traceid>, 0;

-- read trace file
SELECT
	e.Name,
	t.DatabaseName,
	t.NTUserName,
	t.ApplicationName,
	t.CPU,
	t.Duration,
	t.Reads,
	t.RowCounts,
	t.Writes,
	t.TextData
FROM ::fn_trace_gettable('C:\Temp\TS_<MachineName>.trc', default) t
INNER JOIN sys.trace_events e ON t.EventClass = e.trace_event_id
WHERE t.Duration > 0
AND t.EventClass IN (12, 41, 45) -- 12 (SQL:BatchCompleted), 41 (SQL:StmtCompleted), 45 (SP:StmtCompleted)
-- AND t.EventClass = 137           -- 137 Blocked process report
-- AND e.name LIKE 'Blocked process report%'
-- AND t.Duration > 100 * POWER(10, 6);

-- close the trace and delete its definition from the server
EXECUTE sp_trace_setstatus <traceid>, 2;